/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Admin.DAO;

import Admin.Model.BankLoan;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author home
 */
public class BankLoanDAO {
    private Connection myCon;
    private PreparedStatement ps;
    private ResultSet rs;
    private String strCommand = "";

      public ArrayList<BankLoan> GetAll ()
    {
         ArrayList<BankLoan> listBankLoan= new ArrayList<BankLoan>();
         BankLoan bankloan=null;
        try {

            strCommand = "select * from tbl_BankLoan";
            myCon=myConnection.getConnection();
            myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand);

            rs=ps.executeQuery();
            while(rs.next())
            {
                bankloan=new BankLoan();
                bankloan.setBankLoanId(rs.getInt("bankLoanId"));
                bankloan.setBankID(rs.getInt("bankID"));
                bankloan.setBankLoanName(rs.getString("bankLoanName"));
                bankloan.setMaximum(rs.getFloat("Maximum"));
                bankloan.setMinimum(rs.getFloat("Minimum"));
                bankloan.setLoanDescription(rs.getString("loanDescription"));
                bankloan.setDurationOfLoan(rs.getInt("DurationOfLoan"));
                bankloan.setBankLoanStatus(rs.getInt("bankLoanStatus"));

                listBankLoan.add(bankloan);

            }
            return listBankLoan;
        } catch (SQLException ex) {
            Logger.getLogger(BankLoan.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
              myConnection.closeConnection(myCon, ps, rs);
        }
        return null;
    }

     public boolean AddBankLoan(String bankLoanName, int bankID,float Maximum, float Minimum, String loanDescription,int DurationOfLoan)
     {
         boolean flag = false;
        try {

            strCommand = "INSERT INTO tbl_BankLoan (bankLoanName,bankID,Maximum,Minimum,loanDescription,DurationOfLoan,bankLoanStatus) VALUES (?,?,?,?,?,?,1)";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand);
            ps.setString(1, bankLoanName);
            ps.setInt(2, bankID);
            ps.setFloat(3, Maximum);
            ps.setFloat(4, Minimum);
            ps.setString(5, loanDescription);
            ps.setInt(6, DurationOfLoan);
            int i=ps.executeUpdate();
              if (i > 0) {
                flag = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(BankLoanDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
     }

     public BankLoan GetByID (int bankLoanId)
    {

        BankLoan  bankloan=new BankLoan();
        try {

            strCommand = "select * from tbl_BankLoan WHERE bankLoanId=?";

            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand);
            ps.setInt(1, bankLoanId);
            rs=ps.executeQuery();
            while(rs.next())
            {
                bankloan.setBankLoanId(rs.getInt("bankLoanId"));
                bankloan.setBankLoanName(rs.getString("bankLoanName"));
                bankloan.setMaximum(rs.getFloat("Maximum"));
                bankloan.setMinimum(rs.getFloat("Minimum"));
                bankloan.setLoanDescription(rs.getString("loanDescription"));
                bankloan.setDurationOfLoan(rs.getInt("DurationOfLoan"));

            }
            return bankloan;
        } catch (SQLException ex) {
            Logger.getLogger(BankLoan.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
             myConnection.closeConnection(myCon, ps, rs);
        }
        return bankloan;
    }

       public BankLoan GetByBankID (int bankID)
    {

        BankLoan  bankloan=new BankLoan();
        try {

            strCommand = "select * from tbl_BankLoan WHERE bankID=?";

            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand);
            ps.setInt(1, bankID);
            rs=ps.executeQuery();
            while(rs.next())
            {
                bankloan.setBankLoanId(rs.getInt("bankLoanId"));
                bankloan.setBankLoanName(rs.getString("bankLoanName"));
                bankloan.setMaximum(rs.getFloat("Maximum"));
                bankloan.setMinimum(rs.getFloat("Minimum"));
                bankloan.setLoanDescription(rs.getString("loanDescription"));
                bankloan.setDurationOfLoan(rs.getInt("DurationOfLoan"));

            }
            return bankloan;
        } catch (SQLException ex) {
            Logger.getLogger(BankLoan.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
             myConnection.closeConnection(myCon, ps, rs);
        }
        return bankloan;
    }

    public boolean changeStatus(int bankLoanId,int bankLoanStatus)
       {
        boolean flag = false;
        try {
            strCommand = "UPDATE tbl_BankLoan SET bankLoanStatus = ? WHERE bankLoanId = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, bankLoanStatus);
            ps.setInt(2,bankLoanId);
            int i = ps.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
       }

    public boolean editBankLoan(BankLoan objbankloan) {
        //
        boolean flag = false;
        try {
            strCommand = "UPDATE tbl_BankLoan SET bankID = ? ,bankLoanName = ? , Maximum = ? ,Minimum = ?  ,loanDescription = ? ,DurationOfLoan = ? ,bankLoanStatus = ? WHERE bankLoanId = ?  ";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, objbankloan.getBankID());
            ps.setString(2, objbankloan.getBankLoanName());
            ps.setFloat(3, objbankloan.getMaximum());
            ps.setFloat(4, objbankloan.getMinimum());
            ps.setString(5, objbankloan.getLoanDescription());
            ps.setInt(6, objbankloan.getDurationOfLoan());
            ps.setInt(7, objbankloan.getBankLoanStatus());
            ps.setInt(8, objbankloan.getBankLoanId());
            int i = ps.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

      public boolean deleteBankLoan(int bankLoanId)
    {
         boolean flag = false;
        try {
            strCommand = "DELETE FROM tbl_BankLoan WHERE bankLoanId = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, bankLoanId);
            int i = ps.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }
}
